package model

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

// 创建结构体来映射整个 API 返回的 JSON 数据结构
type APIResponse struct {
	Showapi_res_code  int     `json:"showapi_res_code"`
	Showapi_res_error string  `json:"showapi_res_error"`
	Showapi_res_id    string  `json:"showapi_res_id"`
	Showapi_res_body  ResBody `json:"showapi_res_body"`
}

type ResBody struct {
	Ret_code int         `json:"ret_code"`
	List     []BlockInfo `json:"list"`
}

type BlockInfo struct {
	Name      string     `json:"name"`
	ChildList []SubBlock `json:"childList"`
	Code      string     `json:"code"`
}

type SubBlock struct {
	Name      string        `json:"name"`
	Code      string        `json:"code"`
	ChildList []SubSubBlock `json:"childList,omitempty"`
}

type SubSubBlock struct {
	Name      string           `json:"name"`
	Code      string           `json:"code"`
	ChildList []SubSubSubBlock `json:"childList,omitempty"`
}

type SubSubSubBlock struct {
	Name string `json:"name"`
	Code string `json:"code"`
}

func SaveCategory1(apiResponse APIResponse) (err error) {
	// 检查 API 返回的结果
	if apiResponse.Showapi_res_code != 0 {
		log.Fatalf("API Error: %s", apiResponse.Showapi_res_error)
	}

	// 插入数据到数据库
	for _, block := range apiResponse.Showapi_res_body.List {
		// 插入顶层板块信息
		blockID, err := insertBlock(db, block.Name, block.Code, nil, "top")
		if err != nil {
			log.Fatalf("Error inserting block: %v", err)
		}

		// 插入子板块信息
		for _, subBlock := range block.ChildList {
			subBlockID, err := insertBlock(db, subBlock.Name, subBlock.Code, &blockID, "sub")
			if err != nil {
				log.Fatalf("Error inserting sub block: %v", err)
			}

			// 插入子子板块信息（如果有）
			for _, subSubBlock := range subBlock.ChildList {
				_, err := insertBlock(db, subSubBlock.Name, subSubBlock.Code, &subBlockID, "sub_sub")
				if err != nil {
					log.Fatalf("Error inserting sub sub block: %v", err)
				}
			}
		}
	}

	fmt.Println("Data inserted successfully!")

	return nil
}

func SaveCategory(apiResponse APIResponse) error {
	// 检查 API 返回的结果
	if apiResponse.Showapi_res_code != 0 {
		return fmt.Errorf("API Error: %s", apiResponse.Showapi_res_error)
	}

	// 插入数据到数据库
	for _, block := range apiResponse.Showapi_res_body.List {
		// 插入或更新顶层板块信息
		blockID, err := insertOrUpdateBlock(db, block.Name, block.Code, nil, "top")
		if err != nil {
			// return fmt.Errorf("Error inserting/updating block: %v", err)
			return err
		}

		// 插入或更新子板块信息
		for _, subBlock := range block.ChildList {
			subBlockID, err := insertOrUpdateBlock(db, subBlock.Name, subBlock.Code, &blockID, "sub")
			if err != nil {
				// return fmt.Errorf("Error inserting/updating sub block: %v", err)
				return err
			}

			// 插入或更新子子板块信息（如果有）
			for _, subSubBlock := range subBlock.ChildList {
				subsubBlockID, err := insertOrUpdateBlock(db, subSubBlock.Name, subSubBlock.Code, &subBlockID, "sub_sub")
				if err != nil {
					// return fmt.Errorf("Error inserting/updating sub sub block: %v", err)
					return err
				}
				for _, subSubSubBlock := range subSubBlock.ChildList {
					_, err := insertOrUpdateBlock(db, subSubSubBlock.Name, subSubSubBlock.Code, &subsubBlockID, "sub_sub_sub")
					if err != nil {
						// return fmt.Errorf("Error inserting/updating sub sub block: %v", err)
						return err
					}
				}
			}
		}
	}

	fmt.Println("Data inserted or updated successfully!")

	return nil
}

// 插入或更新板块信息
func insertOrUpdateBlock(db *sql.DB, name, code string, parentID *int64, level string) (int64, error) {
	var parentIDValue interface{}
	if parentID == nil {
		parentIDValue = nil
	} else {
		parentIDValue = *parentID
	}

	// 查询是否已存在该 code 的记录
	var existingID int64
	err := db.QueryRow("SELECT id FROM category WHERE code = ? AND name = ?", code, name).Scan(&existingID)
	if err != nil && err != sql.ErrNoRows {
		return 0, err
	}

	if existingID != 0 {
		// 如果存在，则执行更新操作
		_, err := db.Exec("UPDATE category SET name = ?, parent_id = ?, level = ?, updated_at = ? WHERE id = ?", name, parentIDValue, level, time.Now(), existingID)
		if err != nil {
			return 0, err
		}
		return existingID, nil
	}

	// 否则，执行插入操作
	result, err := db.Exec("INSERT INTO category (name, code, parent_id, level) VALUES (?, ?, ?, ?)", name, code, parentIDValue, level)
	if err != nil {
		return 0, err
	}

	// 获取插入数据的 ID
	blockID, err := result.LastInsertId()
	if err != nil {
		return 0, err
	}

	return blockID, nil
}

// 插入板块信息
func insertBlock(db *sql.DB, name, code string, parentID *int64, level string) (int64, error) {
	var parentIDValue interface{}
	if parentID == nil {
		parentIDValue = nil
	} else {
		parentIDValue = *parentID
	}

	// 执行 SQL 插入语句，同时设置 created_at 和 updated_at 字段
	result, err := db.Exec("INSERT INTO category (name, code, parent_id, level) VALUES (?, ?, ?, ?)", name, code, parentIDValue, level)
	if err != nil {
		return 0, err
	}

	// 获取插入数据的 ID
	blockID, err := result.LastInsertId()
	if err != nil {
		return 0, err
	}

	// 更新数据的 updated_at 字段为当前时间
	_, err = db.Exec("UPDATE category SET updated_at = ? WHERE id = ?", time.Now(), blockID)
	if err != nil {
		return 0, err
	}

	return blockID, nil
}

// Category 结构体用于映射数据库中的 category 表记录
type Category struct {
	ID        int           `json:"id"`
	Name      string        `json:"name"`
	Code      string        `json:"code"`
	ParentID  sql.NullInt64 `json:"parent_id"`
	Level     string        `json:"level"`
	CreatedAt time.Time     `json:"created_at"`
	UpdatedAt time.Time     `json:"updated_at"`
}

// GetAllCategories 函数用于从数据库中获取所有 category 记录
func GetAllCategories() ([]Category, error) {
	// 查询所有的 category 记录
	rows, err := db.Query("SELECT id, name, code, parent_id, level, created_at, updated_at FROM category")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var categories []Category

	// 遍历查询结果集
	for rows.Next() {
		var category Category
		var createdAt, updatedAt []uint8 // 数据库中的时间字段以 []uint8 类型表示

		// 扫描数据库字段到 Go 结构体中
		err := rows.Scan(&category.ID, &category.Name, &category.Code, &category.ParentID, &category.Level, &createdAt, &updatedAt)
		if err != nil {
			return nil, err
		}

		// 将 []uint8 类型的数据库时间字段转换为 time.Time 类型
		category.CreatedAt, err = time.Parse("2006-01-02 15:04:05", string(createdAt))
		if err != nil {
			return nil, fmt.Errorf("error parsing created_at: %v", err)
		}

		category.UpdatedAt, err = time.Parse("2006-01-02 15:04:05", string(updatedAt))
		if err != nil {
			return nil, fmt.Errorf("error parsing updated_at: %v", err)
		}

		categories = append(categories, category)
	}

	// 检查 rows.Next() 中的错误
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return categories, nil
}
